import xlrd
import os
import re

# 获取docs目录下的所有xls文件
docs_path = 'c:\\Users\\admin\\Desktop\\新建文件夹\\homework\\docs\\'
files = [f for f in os.listdir(docs_path) if f.endswith('.xls')]

print(f"找到的Excel文件: {files}\n")

# 存储所有学期的课程数据
all_semesters_courses = {}

# 解析课程字符串的函数
def parse_course_info(course_str):
    if not course_str or isinstance(course_str, float):
        return None
    
    # 匹配课程名称、时间、地点、教师
    course_pattern = r'(.+?)/\((\d+)-(\d+)节\)(\d+)-(\d+)周/\s*(.+?)/(.+?)/\d+'
    match = re.search(course_pattern, course_str)
    
    if match:
        return {
            'course_name': match.group(1),
            'start_section': int(match.group(2)),
            'end_section': int(match.group(3)),
            'start_week': int(match.group(4)),
            'end_week': int(match.group(5)),
            'location': match.group(6),
            'teacher': match.group(7)
        }
    return None

# 遍历每个文件，解析课程数据
for file in files:
    file_path = os.path.join(docs_path, file)
    print(f"正在解析文件: {file}")
    
    try:
        # 打开工作簿
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)
        
        # 从第一行获取学期信息
        semester_info = sheet.cell_value(0, 0)
        print(f"学期信息: {semester_info}")
        
        # 存储当前学期的课程
        courses = []
        
        # 遍历表格数据，提取课程信息
        for row_idx in range(2, sheet.nrows):  # 从第3行开始
            for col_idx in range(1, sheet.ncols):  # 从第2列开始是星期几
                cell_value = sheet.cell_value(row_idx, col_idx)
                if cell_value and isinstance(cell_value, str):
                    # 处理可能包含多个课程的单元格
                    if '\r\n' in cell_value:
                        course_list = cell_value.split('\r\n')
                    else:
                        course_list = [cell_value]
                    
                    for course_str in course_list:
                        course_info = parse_course_info(course_str)
                        if course_info:
                            # 获取星期几和节次信息
                            weekday = sheet.cell_value(1, col_idx)  # 第2行是星期几
                            section_info = sheet.cell_value(row_idx, 0)  # 第1列是节次
                            
                            course_info['weekday'] = weekday
                            course_info['section_info'] = section_info
                            courses.append(course_info)
        
        # 存储当前学期的课程
        all_semesters_courses[semester_info] = courses
        print(f"解析到 {len(courses)} 门课程\n")
        
    except Exception as e:
        print(f"读取文件 {file} 时出错: {str(e)}\n")

# 打印解析结果统计
print("=== 解析结果统计 ===")
for semester, courses in all_semesters_courses.items():
    print(f"{semester}: {len(courses)} 门课程")

# 保存解析后的数据到文件
with open('c:\\Users\\admin\\Desktop\\新建文件夹\\homework\\docs\\课程列表.txt', 'w', encoding='utf-8') as f:
    for semester, courses in all_semesters_courses.items():
        f.write(f"\n========== {semester} ==========\n\n")
        
        # 按课程名称分组，避免重复
        course_dict = {}
        for course in courses:
            if course['course_name'] not in course_dict:
                course_dict[course['course_name']] = []
            course_dict[course['course_name']].append(course)
        
        # 输出每门课程的详细信息
        for course_name, course_list in course_dict.items():
            f.write(f"课程名称: {course_name}\n")
            
            # 合并相同课程的不同上课时间
            time_slots = []
            teachers = set()
            locations = set()
            
            for course in course_list:
                time_slot = f"{course['weekday']} {course['section_info']} 第{course['start_week']}-{course['end_week']}周"
                time_slots.append(time_slot)
                teachers.add(course['teacher'])
                locations.add(course['location'])
            
            f.write(f"上课时间: {', '.join(time_slots)}\n")
            f.write(f"授课教师: {', '.join(teachers)}\n")
            f.write(f"上课地点: {', '.join(locations)}\n")
            f.write("-" * 50 + "\n\n")

print("\n课程数据已保存到 'docs\\课程列表.txt' 文件")
